﻿using Dapper;
using Dapper.Contrib.Extensions;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;

namespace SBlogCore.Common.DatabaseAccessor.Repositorys.Dapper
{
    /// <summary>
    /// 数据库类型枚举
    /// </summary>
    public enum DapperDbType
    {
        /// <summary>
        /// SQL  Sever
        /// </summary>
        MsSQL,
        /// <summary>
        /// MySQL
        /// </summary>
        MySQL
    }
    /// <summary>
    /// Dapper帮助类
    /// </summary>
    [SkipScan]
    public sealed class DapperRepository : IDapperRepository
    {
        private string? _dbname;
        private DapperDbType _type;
        private string sqlConnection;
        private readonly IConfiguration _configuration;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="configuration"></param>
        public DapperRepository(IConfiguration configuration)
        {
            _configuration = configuration;
            sqlConnection = configuration.GetConnectionString("SBlogCore");
        }
        /// <summary>
        /// 切换数据库
        /// </summary>
        /// <param name="dbname"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public DapperRepository SelectDb(string dbname, DapperDbType type)
        {
            if (string.IsNullOrEmpty(dbname))
            {
                throw new System.Exception("数据库连接串名称不能为空");
            }
            this._dbname = dbname;
            this._type = type;
            sqlConnection = _configuration.GetConnectionString(dbname);
            if (string.IsNullOrEmpty(sqlConnection))
            {
                throw new System.Exception($"未找到名为{dbname}的连接串，请到AppSetting.Json 中配置！");
            }
            return this;
        }
        /// <summary>
        /// 链接数据库
        /// </summary>
        /// <returns></returns>
        public IDbConnection GetConn()
        {
            if (string.IsNullOrEmpty(sqlConnection) && string.IsNullOrEmpty(_dbname))
                sqlConnection = _configuration.GetConnectionString(_dbname);
            IDbConnection conn = default!;
            switch (_type)
            {
                case DapperDbType.MySQL:
                    conn = new MySqlConnection(sqlConnection);
                    break;
                default:
                    if (string.IsNullOrEmpty(sqlConnection))
                        sqlConnection = _configuration.GetConnectionString("OpenAuthDBContext");
                    conn = new SqlConnection(sqlConnection);
                    break;
            }
            conn.Open();
            return conn;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SPName"></param>
        /// <returns></returns>
        public List<T> Query<T>(string SPName)
        {
            using (IDbConnection Conn = GetConn())
            {
                return Conn.Query<T>(SPName, null, null, true, 0, CommandType.StoredProcedure).AsList();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SPName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public List<T> Query<T>(string SPName, object parms)
        {
            using (IDbConnection Conn = GetConn())
            {
                return Conn.Query<T>(SPName, parms, null, true, 0, CommandType.StoredProcedure).AsList();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SPName"></param>
        /// <returns></returns>
        public async Task<List<T>> QueryAsync<T>(string SPName)
        {
            using (IDbConnection Conn = GetConn())
            {
                // return await Task.Run(() => conn.Query<T>(SPName, null, null, true, null, CommandType.StoredProcedure).ToList());
                return (await Conn.QueryAsync<T>(SPName, null, null, 0, CommandType.StoredProcedure)).AsList();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SPName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public async Task<List<T>> QueryAsync<T>(string SPName, object parms)
        {
            using (IDbConnection Conn = GetConn())
            {
                return (await Conn.QueryAsync<T>(SPName, parms, null, 0, CommandType.StoredProcedure)).AsList();
            }
        }

        /// <summary>
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="deleteSql"></param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(int Id, string deleteSql)
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.ExecuteAsync(deleteSql, new { Id = Id });
            }
        }

        /// <summary>
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="detailSql"></param>
        /// <returns></returns>
        public async Task<T> DetailFirstAsync<T>(int Id, string detailSql)
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.QueryFirstOrDefaultAsync<T>(detailSql, new { Id = Id });
            }
        }

        /// <summary>
        /// 自定义执行多表事务存储过程
        /// </summary>
        /// <param name="SPName">存储过程过程名</param>
        /// <param name="parms">参数</param>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public async Task<int> ExecSPTransAsync(string SPName, object parms, IDbConnection conn, IDbTransaction tran)
        {
            if (conn.State == ConnectionState.Open)
            {
                return await conn.ExecuteAsync(SPName, parms, tran, 20, CommandType.StoredProcedure);
            }
            else
            {
                return -1;
            }

        }

        /// <summary>
        /// 执行中包含事务
        /// </summary>
        /// <param name="SPName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public async Task<int> ExecSPTransAsync(string SPName, object parms)
        {

            using (IDbConnection Conn = GetConn())
            {
                using (IDbTransaction tran = Conn.BeginTransaction())
                {

                    var ret = await ExecSPTransAsync(SPName, parms, Conn, tran);
                    tran.Commit();
                    return ret;

                }
            }

        }

        /// <summary>
        /// 不带事务的执行存储过程
        /// </summary>
        /// <param name="SPName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public async Task<int> ExecSPAsync(string SPName, object parms)
        {

            using (IDbConnection Conn = GetConn())
            {
                return await ExecSPTransAsync(SPName, parms, Conn, null);
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="parms"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public async Task<DataSet> QueryDataSetAsync(string Sql, object parms, CommandType commandType)
        {
            var ds = new DataSet();
            using (IDbConnection Conn = GetConn())
            {
                var reader = await Conn.ExecuteReaderAsync(Sql, parms, null, 0, commandType);
                int i = 0;
                while (!reader.IsClosed)
                {
                    ds.Tables.Add($"table{i}");
                    ds.Tables[i].Load(reader);
                    i++;
                }
            }
            return ds;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="insertSql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public async Task<int> InsertAsync(string insertSql, object parms)
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.ExecuteAsync(insertSql, parms);
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="selectSql"></param>
        /// <returns></returns>
        public async Task<List<T>> SelectAsync<T>(string selectSql)
        {
            using (IDbConnection Conn = GetConn())
            {
                return (await Conn.QueryAsync<T>(selectSql)).AsList();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="updateSql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(string updateSql, object parms)
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.ExecuteAsync(updateSql, parms);
            }
        }

        /// <summary>
        /// 获取所有数据
        /// </summary>
        /// <returns>所有数据</returns>
        public async Task<IEnumerable<T>> QueryAllAsync<T>()
             where T : class
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.GetAllAsync<T>();
            }
        }

        /// <summary>
        /// 通过主键查询数据。
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns>查询结果</returns>
        public async Task<T> QueryOneAsync<T>(int id)
             where T : class
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.GetAsync<T>(id);
            }
        }
        /// <summary>
        /// 插入一组数据并且返回自动生成的主键的值
        /// </summary>
        /// <param name="entity">要插入的数据</param>
        /// <returns>自动生成的主键的值</returns>
        public async Task<long> InsertByEntityAsync<T>(T entity)
             where T : class
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.InsertAsync(entity);
            }
        }

        /// <summary>
        /// 批量插入数据并且返回插入数据的个数
        /// </summary>
        /// <param name="entitys">要插入的数据</param>
        /// <returns>数量</returns>
        public async Task<long> InsertByEntitysAsync<T>(IEnumerable<T> entitys)
             where T : class
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.InsertAsync(entitys);
            }
        }

        /// <summary>
        /// 根据主键修改数据
        /// </summary>
        /// <param name="entity">修改后的数据</param>
        /// <returns>是否修改成功</returns>
        public async Task<bool> UpdateByEntityAsync<T>(T entity)
             where T : class
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.UpdateAsync<T>(entity);
            }
        }

        /// <summary>
        /// 批量修改数据
        /// </summary>
        /// <param name="entitys">实体模型</param>
        /// <returns></returns>
        public async Task<bool> UpdateByEntitysAsync<T>(IEnumerable<T> entitys)
             where T : class
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.UpdateAsync(entitys);
            }
        }


        /// <summary>
        /// 根据主键删除数据
        /// </summary>
        /// <param name="entity">实体模型</param>
        /// <returns></returns>
        public async Task<bool> DeleteByEntityAsync<T>(T entity) where T : class
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.DeleteAsync<T>(entity);
            }
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="entitys">实体模型</param>
        /// <returns></returns>
        public async Task<bool> DeleteByEntitysAsync<T>(IEnumerable<T> entitys)
             where T : class
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.DeleteAsync(entitys);
            }
        }
        /// <summary>
        /// 查找唯一记录，如果存在多个记录，则异常
        /// </summary>
        /// <typeparam name="T">返回类型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns></returns>
        public async Task<T> QuerySingleOrDefaultAsync<T>(string sql, object parms)
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.QuerySingleOrDefaultAsync<T>(sql, parms);
            }
        }

        /// <summary>
        /// 默认返回第一条数据
        /// </summary>
        /// <typeparam name="T">返回类型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public async Task<T> QueryFirstOrDefaultAsync<T>(string sql)
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.QueryFirstOrDefaultAsync<T>(sql);
            }
        }

        /// <summary>
        /// 根据查询SQL直接返回datatable
        /// </summary>
        /// <param name="entitys">实体模型</param>
        /// <returns></returns>
        public async Task<DataTable> QueryToDatableAsync(string querySQL)
        {
            using (IDbConnection Conn = GetConn())
            {
                DataTable myDatatable = new DataTable("myDatatable");
                var reader = await Conn.ExecuteReaderAsync(querySQL);
                myDatatable.Load(reader);
                return myDatatable;
            }
        }

        /// <summary>
        /// 根据查询SQL直接返回datatable
        /// </summary>
        /// <param name="entitys">实体模型</param>
        /// <returns></returns>
        public async Task<DataTable> QueryToDatableAsync(string querySQL, DynamicParameters parms)
        {
            using (IDbConnection Conn = GetConn())
            {
                DataTable myDatatable = new DataTable("myDatatable");
                var reader = await Conn.ExecuteReaderAsync(querySQL, parms);
                myDatatable.Load(reader);
                return myDatatable;
            }
        }

        /// <summary>
        /// 执行SQL语句返回结果gjg
        /// </summary>
        /// <param name="insertSql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public async Task<T> ExecuteScalarAsync<T>(string Sql, object parms)
        {
            using (IDbConnection Conn = GetConn())
            {
                return await Conn.ExecuteScalarAsync<T>(Sql, parms);
            }
        }
        /// <summary>
        /// 执行中包含事务sql
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public async Task<int> ExecSqlTransAsync(List<string> listSql)
        {
            int iResult = 0;
            using (IDbConnection Conn = GetConn())
            {
                using (IDbTransaction tran = Conn.BeginTransaction())
                {
                    try
                    {
                        foreach (var sql in listSql)
                        {
                            await Conn.ExecuteAsync(sql, null);
                        }
                        tran.Commit();
                        iResult = 1;
                    }
                    catch
                    {
                        tran.Rollback();
                        iResult = -1;
                    }
                }
            }
            return iResult;
        }
    }
}
